<?php
class myPDO {
    private $pdo;
    public static $instance = NULL;

    private function __construct() 
    {
    	/* Connect to an ODBC database using driver invocation */
		/*
		// mysql:
		$dsn = 'mysql:dbname=wordage;host=127.0.0.1';
		$user = 'root';
		$password = '696969';
		try {
			$this->pdo = new PDO($dsn, $user, $password);
		} catch (Exception $e) {
			die("Nepavyko prisijungti");
		}
		*/
		
		
		// sqlite:
		// used sqlite-specific functions:
		// Random()
    	$this->pdo = new PDO("sqlite:" . DB_FILENAME, null, null, array(PDO::ATTR_PERSISTENT => true));
		
		
        $this->pdo->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
        $this->pdo->setAttribute(PDO::ATTR_EMULATE_PREPARES, true);
        $this->pdo->setAttribute(PDO::ATTR_DEFAULT_FETCH_MODE, PDO::FETCH_ASSOC);
    }

    public static function getInstance() 
    {
        if(!isset(self::$instance)) 
        {
            self::$instance = new myPDO();
        }        
        return self::$instance;
    }
  
    public function prepare($paramSql) 
    {
        return $this->pdo->prepare($paramSql);    
    }  

    public function fetch_rows($sql) 
    {
        $rst = $this->pdo->query($sql);
        return $rst->fetchAll();
    }

    public function fetch_row($sql) 
    {
        $rst = $this->pdo->query($sql);
        return $rst->fetch();
    }  
}

class DbCommunicator
{                                              
    private static $DEFAULT_USER_ID = 1;
    private static $DEFAULT_DICT_ID = 1;
    
    public static function userAdmittedToDict($dbh, $user_id, $dict_id)
    {    	
    	$sql = "SELECT user_id FROM user_dict WHERE user_id=:user_id AND dict_id=:dict_id";
    	$prep = $dbh->prepare($sql);
    	$prep->bindParam(':user_id', $user_id, PDO::PARAM_INT);
    	$prep->bindParam(':dict_id', $dict_id, PDO::PARAM_INT);
    	$prep->execute();
    	// Logger::log ("allowed $user_id to $dict_id:", !!$prep->fetch(PDO::FETCH_ASSOC));
    	// grąžinamas tik pirmasis - tik tam, kad nustatyti true ar false
    	return $prep->fetch(PDO::FETCH_ASSOC);
    }
    
    public static function fetchAllDictsData($dbh, $current_user_id)
    {
    	// current user negali būti nulis, nes šita užklausa vykdoma autorizuotam vartotojui
    	$sql = 'SELECT dicts.id, dicts.name, dicts.descr, EXISTS (SELECT 1 FROM user_dict WHERE user_dict.user_id=:uid AND user_dict.dict_id=dicts.id AND user_dict.managed=0) AS subscribed, EXISTS (SELECT 1 FROM user_dict WHERE user_dict.user_id=:uid AND user_dict.dict_id=dicts.id AND user_dict.managed<>0) AS managed FROM dicts ORDER BY subscribed DESC, managed DESC, dicts.name';
		$prep = $dbh->prepare($sql);
		$prep->bindParam(':uid', $current_user_id, PDO::PARAM_INT);
		$prep->execute();
		return $prep->fetchAll(PDO::FETCH_ASSOC);
    }
    
    public static function fetchUserDicts($dbh, $user_id)
    {
    	if ($user_id == 0) 
    	{
    		$user_id = self::$DEFAULT_USER_ID;
    	}
    	
    	$sql = 'SELECT * FROM dicts INNER JOIN user_dict ON dicts.id=user_dict.dict_id WHERE user_dict.user_id=:user_id';
		$prep = $dbh->prepare($sql);		
		$prep->bindParam(':user_id', $user_id, PDO::PARAM_INT);
		$prep->execute();
		return $prep->fetchAll(PDO::FETCH_ASSOC);    	
    }
    
	public static function fetchRandomRow($dbh, $dict_id)
	{
    	if ($dict_id == 0) 
    	{
    		$dict_id = self::$DEFAULT_DICT_ID;
    	}
		// sqlite:
    	$sql = 'SELECT * FROM (SELECT * FROM items WHERE dict_id=:dict_id) ORDER BY Random() LIMIT 1';
		// $sql = 'SELECT * FROM items ORDER BY Random() LIMIT 1;';
    	$prep = $dbh->prepare($sql);
    	$prep->bindParam(':dict_id', $dict_id, PDO::PARAM_INT);
    	$prep->execute();
    	return $prep->fetch(PDO::FETCH_ASSOC);
		// mysql:
		//$sql = 'SELECT * FROM items WHERE id >= (SELECT FLOOR(MAX(id) * RAND()) FROM items) ORDER BY id LIMIT 1;';		
	}
	
	public static function fetchItemsByString($dbh, $string)
	{
		$str = strtolower($string);
		if ($string === "***") {
			$sql = "SELECT id, mother, fore FROM items";
		} else {
			$sql = "SELECT id, mother, fore FROM items WHERE lower(mother) LIKE '%$str%' OR lower(fore) LIKE '%$str%'";
		}
		return $dbh->query($sql)->fetchAll();
	}

	public static function fetchIdPasswordByEmail($dbh, $email)
	{
		$emailhash = MD5($email);
		$sql = "SELECT id, password FROM users WHERE email='$emailhash'";
		return $dbh->query($sql)->fetch();
	}
	
	public static function isInDb($dbh, $word)
	{
		$sql = "SELECT id FROM items WHERE mother=:word OR fore=:word";
		$prep = $dbh->prepare($sql);
		$prep->bindParam(':word', $word, PDO::PARAM_STR);
		$prep->execute();
		// grąžinamas tik pirmasis - tik tam, kad nustatyti true ar false
		return $prep->fetch(PDO::FETCH_ASSOC);
	}
	
	public static function isInDbDifferentId($dbh, $word, $id)
	{
		$sql = "SELECT id FROM items WHERE (mother=:word OR fore=:word) AND id<>:id";
		$prep = $dbh->prepare($sql);
		$prep->bindParam(':word', $word, PDO::PARAM_STR);
		$prep->bindParam(':id', $id, PDO::PARAM_INT);
		$prep->execute();
		// grąžinamas tik pirmasis - tik tam, kad nustatyti true ar false
		return $prep->fetch(PDO::FETCH_ASSOC);
	}
	
	public static function createNewDict($dbh, $name, $description, $user_id)
	{
		// du veiksmai sukišti į vieną procedūrą (nes viskas atliekama viena transakcija):
		// sukuriamas naujas įrašas dict lentelėje
		// ir tas įrašas surišamas su egzistuojančiu user_id
		
		// šitas sukuria naują žodyną
		$sqlCreate = "INSERT INTO dicts (id, name, descr) VALUES (null, :name, :descr)";
		$prepCreate = $dbh->prepare($sqlCreate);
		$prepCreate->bindParam(':name', $name, PDO::PARAM_STR);
		$prepCreate->bindParam(':descr', $description, PDO::PARAM_STR);
		
		// šitas pririša žodyną prie vartotojo
		$sqlBind = "INSERT INTO user_dict (user_id, dict_id, managed) VALUES (:user_id, :dict_id, 1)";
		$prepBind = $dbh->prepare($sqlBind);
		$prepBind->bindParam(':user_id', $user_id, PDO::PARAM_INT);	
		
		// transakcija
		try 
		{
			$dbh->beginTransaction();
			$prepCreate->execute();
			$dict_id = $dbh->lastInsertId();
			$prepBind->bindParam(':dict_id', $dict_id, PDO::PARAM_INT);
			$prepBind->execute();
			$dbh->commit();
		} 
		catch(PDOExecption $e) 
		{
			$dbh->rollback();
			$dict_id = 0;
		}
		
		return $dict_id;
	}
	
	public static function createDictDuplicate ($dbh, $user_id, $dict_id)
	{
		// šitas sukuria naują žodyną
		$sqlCreate = "INSERT INTO dicts (name, descr) SELECT name, descr FROM dicts WHERE id=:id";
		$prepCreate = $dbh->prepare($sqlCreate);
		$prepCreate->bindParam(':id', $dict_id, PDO::PARAM_INT);		
		
		// šitas pririša naująjį žodyną pre vartotojo
		$sqlBind = "INSERT INTO user_dict (user_id, dict_id, managed) VALUES (:user_id, :dict_id, 1)";
		$prepBind = $dbh->prepare($sqlBind);
		$prepBind->bindParam(':user_id', $user_id, PDO::PARAM_INT);
		
		// šitas pagamina senojo žodyno items duplicatus ir jiems priskiria naujojo žodyno dict_id
		$sqlDuplicateItems = 
		"INSERT INTO items (dict_id, mother, fore) SELECT :new_dict_id, mother, fore FROM items WHERE dict_id=:original_dict_id";
		$prepDuplicateItems = $dbh->prepare($sqlDuplicateItems);
		$prepDuplicateItems->bindParam(':original_dict_id', $dict_id, PDO::PARAM_INT);
		
		// transakcija
		try 
		{
			$dbh->beginTransaction();
			$prepCreate->execute();
			$new_dict_id = $dbh->lastInsertId();
			$prepBind->bindParam(':dict_id', $new_dict_id, PDO::PARAM_INT);
			$prepDuplicateItems->bindParam(':new_dict_id', $new_dict_id, PDO::PARAM_INT);
			$prepDuplicateItems->execute();
			$dbh->commit();
		} 
		catch(PDOExecption $e) 
		{
			$dbh->rollback();
			$new_dict_id = 0;
		}
		
		return $new_dict_id;
	}
	
	public static function insertItem($dbh, $mother, $fore)
	{		
		$sql = "INSERT INTO items (id, mother, fore) VALUES (null, :mother, :fore)";
		$prep = $dbh->prepare($sql);
		$prep->bindParam(':mother', $mother, PDO::PARAM_STR);		
		$prep->bindParam(':fore', $fore, PDO::PARAM_STR);
        $result = $prep->execute();
		return $result;
	}
	
	public static function updateItem($dbh, $id, $mother, $fore)
	{		
		$sql = "UPDATE items SET mother=:mother, fore=:fore WHERE id=:id";
		$prep = $dbh->prepare($sql);
		$prep->bindParam(':id', $id, PDO::PARAM_INT);
		$prep->bindParam(':mother', $mother, PDO::PARAM_STR);		
		$prep->bindParam(':fore', $fore, PDO::PARAM_STR);
        $result = $prep->execute();
		return $result;
	}		
	
	public static function deleteItemById($dbh, $id)
	{		
		$sql = "DELETE FROM items WHERE id=:id";
		$prep = $dbh->prepare($sql);
		$prep->bindParam(':id', $id, PDO::PARAM_INT);	
        $result = $prep->execute();
		return $result;
	}
	
	public static function truncateTable($dbh)
	{		
		$sql = "DELETE FROM items";
		$prep = $dbh->prepare($sql);
        $result = $prep->execute();
		return $result;
	}
	
	public static function fetchAllRows($dbh)
	{
		$sql = "SELECT mother, fore FROM items";
		return $dbh->query($sql)->fetchAll();
	}
	
	public static function unsubscribeDict($dbh, $user_id, $dict_id)
	{		
		// ištrinti įrašą user_dict
		$sql = "DELETE FROM user_dict WHERE user_id=:user_id AND dict_id=:dict_id";	
		$prep = $dbh->prepare($sql);
		$prep->bindParam(':user_id', $user_id, PDO::PARAM_INT);		
		$prep->bindParam(':dict_id', $dict_id, PDO::PARAM_INT);
        return $prep->execute();
	}
	
	public static function subscribeDict($dbh, $user_id, $dict_id)
	{		
		// sukurti originalų įrašą user_dict
		$sql = "INSERT INTO user_dict (user_id, dict_id) VALUES (:user_id, :dict_id)";
		$prep = $dbh->prepare($sql);
		$prep->bindParam(':user_id', $user_id, PDO::PARAM_INT);		
		$prep->bindParam(':dict_id', $dict_id, PDO::PARAM_INT);
        return $prep->execute();
	}
	
	public static function isSubscribed($dbh, $user_id, $dict_id)
	{
		// tikrinti ar tas dict dar naudojamas (ar egzistuoja dict_id user_dict lentelėje)
		$sql = "SELECT dict_id FROM user_dict WHERE user_id=:user_id AND dict_id=:dict_id";
		$prep = $dbh->prepare($sql);
		$prep->bindParam(':user_id', $user_id, PDO::PARAM_INT);		
		$prep->bindParam(':dict_id', $dict_id, PDO::PARAM_INT);
		$prep->execute();
		return $prep->fetch(PDO::FETCH_ASSOC);		
	}
	
	public static function dictIsInUse($dbh, $dict_id) 
	{
		// tikrinti ar tas dict dar naudojamas (ar egzistuoja dict_id user_dict lentelėje)
		$sql = "SELECT dict_id FROM user_dict WHERE dict_id=:dict_id";
		$prep = $dbh->prepare($sql);
		$prep->bindParam(':dict_id', $dict_id, PDO::PARAM_INT);
		$prep->execute();
		return $prep->fetch(PDO::FETCH_ASSOC);
	}
	
	public static function deleteItems($dbh, $dict_id) 
	{		
		// ištrinti įrašus, kurių dict_id yra $dict_id
		$sql = "DELETE FROM items WHERE dict_id=:dict_id";
		$prep = $dbh->prepare($sql);
		$prep->bindParam(':dict_id', $dict_id, PDO::PARAM_INT);
        return $prep->execute();		
	}	
}